package news.dao.empl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import news.dao.DaoFactory;
import news.dao.INewsDao;
import news.entity.Admin;
import news.entity.Board;
import news.entity.News;

public class NewsDaoImpl implements INewsDao {
	Connection conn=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	
	public List<News> getAllNews() {
		List<News> list=new ArrayList<News>();
		try {
			conn=DaoFactory.getConn();
			String sql="select * from tb_board,tb_news,tb_admin where tb_board.boardid=tb_news.boardid and tb_admin.adminid=tb_news.adminid order by tb_news.newsid desc";
			/*select * from tb_admin a inner join 
			  (select n.newsid,n.newstitle,n.newsctime,n.newscontent,n.newsimg,n.adminid,n.boardid,b.boardname
 				from tb_news n inner join tb_board b on n.boardid=b.boardid*/
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				//声明一个news类型的变量来接rs中的数据，然后将他放到list数组中 
				/*News news=new News(
					rs.getInt("newsid"),
					rs.getString("newstitle"),
					rs.getDate("newsctime"),
					rs.getString("newscontent"),
					rs.getString("newsimg"),
					new Admin(rs.getInt("adminid")),
					new Board(rs.getInt("boardid"))
				);*/
				News news=new News();
				news.setNewId(rs.getInt("newsid"));
				news.setNewsTitle(rs.getString("newstitle"));
				news.setNewsCTime(rs.getDate("newsctime"));				
				news.setNewsContent(rs.getString("newscontent"));
				news.setNewsImg(rs.getString("newsimg"));
				
				//将查出来的adminname也放到admin中
				Admin admin=new Admin();
				admin.setAdminId(rs.getInt("adminid"));
				admin.setAdminName(rs.getString("adminname"));
				news.setAdminId(admin);
				
				Board board=new Board();
				board.setBoardId(rs.getInt("boardid"));
				board.setBoardName(rs.getString("boardname"));
				news.setBoardId(board);
				
				list.add(news);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	
	 
	public boolean addNews(News news) {
		boolean flag=false;
		try {
			conn=DaoFactory.getConn();
			String sql="insert into tb_news(newsid,newstitle,newscontent,newsctime,adminid,boardid,newsimg) values(news_seq.nextval,?,?,?,?,?,?)";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, news.getNewsTitle());
			pstmt.setString(2, news.getNewsContent());
			pstmt.setDate(3, new java.sql.Date(news.getNewsCTime().getTime()));
			pstmt.setInt(4, news.getAdminId().getAdminId());
			pstmt.setInt(5, news.getBoardId().getBoardId());
			pstmt.setString(6, news.getNewsImg());
			
			int result=pstmt.executeUpdate();
			if(result>0){
				flag=true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}



	
	public int getNewsCount() {
		int count=0;
		try {
			conn=DaoFactory.getConn();
			String sql="select count(newsid) from tb_news";
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			if(rs.next()){
				count=rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}



	
	public List<News> getNewsByPage(int pageSize, int currentPage) {
		List<News> list=new ArrayList<News>();
		try {
			conn=DaoFactory.getConn();
			String sql="select * from (select t.*,rownum rn from (select * from tb_admin a inner join (select n.newsid,n.newstitle,n.newsctime,n.newscontent,n.newsimg,n.adminid,n.boardid,b.boardname  from tb_news n inner join tb_board b on n.boardid=b.boardid order by n.newsid desc) q on a.adminid=q.adminid) t where rownum<="+pageSize*currentPage+") where rn>="+(pageSize*(currentPage-1)+1)+"";
			
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				
				News news=new News();
				news.setNewId(rs.getInt("newsid"));
				news.setNewsTitle(rs.getString("newstitle"));
				news.setNewsCTime(rs.getDate("newsctime"));				
				news.setNewsContent(rs.getString("newscontent"));
				news.setNewsImg(rs.getString("newsimg"));
				
				//将查出来的adminname也放到admin中
				Admin admin=new Admin();
				admin.setAdminId(rs.getInt("adminid"));
				admin.setAdminName(rs.getString("adminname"));
				news.setAdminId(admin);
				
				Board board=new Board();
				board.setBoardId(rs.getInt("boardid"));
				board.setBoardName(rs.getString("boardname"));
				news.setBoardId(board);
				
				list.add(news);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
		
	}



	@Override
	public List<News> getNewsByBoardId(int boardId) {
		// TODO Auto-generated method stub
		return null;
	}



	@Override
	public News getNewsByNewsId(int newsId) {
		// TODO Auto-generated method stub
		return null;
	}

}
